﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using WmsApp.Security;
using WmsApp.WmsService;

namespace WmsApp.Export
{
    public class OutStoreBillExcelImport:IOutStoreBillExcelImport
    {
        public List<ExtendStoreGoods> ReadExtendStoreGoodsListFromExcel(string excelFile)
        {
            var outStoreBillDtls = new List<ExtendStoreGoods>();
            DataTable excelTable = null;
            var ds = new DataSet();

            var conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;");
            conn.Open();
            var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            string strSql = "select * from [" + tableName + "]";
            var myData = new OleDbDataAdapter(strSql, conn);
            myData.Fill(ds, tableName);
            conn.Close();
            excelTable = ds.Tables[tableName];
            var client = Factory.WmsServiceFactory.GetServiceClient();

            foreach (DataRow row in excelTable.Rows)
            {
                if (String.IsNullOrEmpty(row["物料代码"].ToString().Trim()))
                {
                    continue;
                }
                string newGoodsCode = row["物料代码"].ToString().Trim();
                decimal qty = 0;
                if (! Decimal.TryParse(row["数量"].ToString().Trim(), out qty))
                {
                    throw new Exception(String.Format("物料代码{0}的数量字段格式不正确！", newGoodsCode));
                }
                var list = client.GetExtendStoreGoodsListByCondition(String.Format(" and NewGoodsCode = '{0}'",newGoodsCode));
                if(list.Count ==0)
                {
                    throw new Exception(String.Format("物料代码{0}在库存中不存在！", newGoodsCode));
                }
                var goods  = list[0];
                goods.Quantity = qty;
                outStoreBillDtls.Add(goods);
            }

            return outStoreBillDtls;
        }
    }
}
